<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Facades\DB;
use Watson\Validating\ValidatingTrait;
use App\Models\StudentWorksView;

class GgrSchool extends Model
{
    use SoftDeletes;
    use ValidatingTrait;

    const CACHE_TAG = 'ggr-school';
    const LIST_CACHE_TAG = 'ggr-school-list';

    protected $table = 'ggr_schools';

    protected $fillable = ['school_id', 'teacher_id' , 'leader_id' , 'admin_id' , 'dept_id' , 'speciality_id'];

    protected $rules = [
        'school_id'          => 'required',
        'teacher_id'         => 'required',
    //    'leader_id'          => 'required',
        'admin_id'           => 'required',
    ];

    protected $validationMessages = [
        'school_id.required'          => '学校必填',
        'teacher_id.required'         => '联系人必填',
      //  'leader_id.required'          => '领导必填',
        'admin_id.required'           => '负责人必填',
    ];

    /**
     * 执行老师
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function teacher()
    {
        return $this->belongsTo('\App\Models\Teacher', 'teacher_id')->withTrashed();
    }

    /**
     * 院校领导
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function leader()
    {
        return $this->belongsTo('\App\Models\Teacher', 'leader_id')->withTrashed();
    }

    /**
     * 广告人责编
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function ggr()
    {
        return $this->belongsTo('\App\Models\AdminInfo', 'admin_id')->withTrashed();
    }

    /**
     * 联系院校
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function school()
    {
        return $this->belongsTo('\App\Models\School', 'school_id')->withTrashed();
    }

    /**
     * 联系学院
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function dept()
    {
        return $this->belongsTo('\App\Models\School', 'dept_id')->withTrashed();
    }

    /**
     * 联系专业
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function speciality()
    {
        return $this->belongsTo('\App\Models\School', 'speciality_id')->withTrashed();
    }

    /**
     * 学校荣誉
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function honors()
    {
        return $this->belongsToMany('\App\Models\SchoolHonor' , 'xyj_school_honor' , 'ggr_school_id' , 'honor_id');
    }

    /**
     * 专业方向
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function specialityOrientations()
    {
        return $this->belongsToMany('\App\Models\SpecialityOrientation' , 'xyj_speciality_orientation_school' , 'ggr_school_id' , 'speciality_orientation_id');
    }


    /**
     * 联系记录
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function contacts()
    {
        return $this->hasMany('\App\Models\GgrSchoolContact' , 'ggr_schools_id');
    }


    /**
     * 邮寄记录
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function ems()
    {
        return $this->hasMany('\App\Models\GgrSchoolEms' , 'ggr_school_id');
    }


    /**
     * 统计学校获奖情况
     * @return array
     */
    public function getSchoolVoteAwards()
    {
        //方法一
        /**
        $searchAwardSettings = DB::table('xyj_works_award_search_setting')->groupBy('award_id')->lists('award_id');
        $searchVoteAwardSettings = DB::table('xyj_works_award_search_setting')->groupBy('voteAward_id')->lists('voteAward_id');
        $countNumbers = Works::join('works_author' , 'works_author.works_id' , '=' , 'works.id')
            ->join('xyj_student' , 'xyj_student.user_id' , '=' , 'works_author.author_id')
            ->select(DB::raw('works.award_id , works.vote_award_id , count(works.id) as count_number , xyj_student.school_id '))
            ->where('xyj_student.school_id' , $this->school_id)
            ->whereIn('works.award_id' , $searchAwardSettings)
            ->whereIn('works.vote_award_id' , $searchVoteAwardSettings)
            ->groupBy('works.award_id' , 'works.vote_award_id')->get();
        foreach ($countNumbers as $countNumber){
            $result[$countNumber->award->name][$countNumber->voteAward->name] =  $countNumber->count_number;
        }
        $countAwardNumbers = Works::join('works_author' , 'works_author.works_id' , '=' , 'works.id')
            ->join('xyj_student' , 'xyj_student.user_id' , '=' , 'works_author.author_id')
            ->select(DB::raw('works.award_id , works.vote_award_id , count(works.id) as count_number , xyj_student.school_id '))
            ->where('xyj_student.school_id' , $this->school_id)
            ->whereIn('works.award_id' , $searchAwardSettings)
            ->groupBy('works.award_id')->get();
        foreach ($countAwardNumbers as $countAwardNumber){
            $result[$countAwardNumber->award->name]['作品总计'] =  $countAwardNumber->count_number;
        }
         * **/

        //方法二

/**
        $staticResult =  \Cache::tags(self::CACHE_TAG)->rememberForever('ggr-school:' . $this->school_id . '-' . $this->dept_id . '-' .$this->speciality_id , function (){
            $result = [];
            $searchAwardSettings = DB::table('xyj_works_award_search_setting')->groupBy('award_id')->get();
            foreach ($searchAwardSettings as $searchAwardSetting){
                $awardName = Award::find($searchAwardSetting->award_id)->name;
                $searchVoteAwardSettings = DB::table('xyj_works_award_search_setting')->where('award_id' , $searchAwardSetting->award_id)->get();
                foreach ($searchVoteAwardSettings as $searchVoteAwardSetting){
                    $voteAwardName = VoteAward::find($searchVoteAwardSetting->voteAward_id)->name;
                    $count = DB::table('student_works_view')->where(['school_id' => $this->school_id , 'award_id' => $searchAwardSetting->award_id , 'vote_award_id' => $searchVoteAwardSetting->voteAward_id]);
                    if ($this->dept_id){
                        $count->where('dept_id' , $this->dept_id);
                        if ($this->speciality_id){
                            $count->where('speciality_id' , $this->speciality_id);
                        }
                    }
                    $count = $count->count();
                    $result[$awardName][$voteAwardName] =  $count;
                }
                $num = DB::table('student_works_view')->where(['school_id' => $this->school_id , 'award_id' => $searchAwardSetting->award_id ]);
                if ($this->dept_id){
                    $num->where('dept_id' , $this->dept_id);
                    if ($this->speciality_id){
                        $num->where('speciality_id' , $this->speciality_id);
                    }
                }
                $num = $num->count();
                $result[$awardName]['作品总计'] =  $num;
            }
            return json_encode($result, JSON_UNESCAPED_UNICODE );
        });
        return json_decode($staticResult, true);

**/



        //方法三

        $staticResult =  \Cache::tags(self::CACHE_TAG)->rememberForever('ggr-school:' . $this->school_id . '-' . $this->dept_id . '-' .$this->speciality_id , function (){
            $result = [];
            $searchAwardSettings = DB::table('xyj_works_award_search_setting')->groupBy('award_id')->get();
            $collection = StudentWorksView::where(['school_id' => $this->school_id ]);
            if ($this->dept_id){
                $collection->where('dept_id' , $this->dept_id);
                if ($this->speciality_id){
                    $collection->where('speciality_id' , $this->speciality_id);
                }
            }
            $collection = $collection->get();
            foreach ($searchAwardSettings as $searchAwardSetting){
                $awardName = Award::find($searchAwardSetting->award_id)->name;
                $searchVoteAwardSettings = DB::table('xyj_works_award_search_setting')->where('award_id' , $searchAwardSetting->award_id)->get();
                foreach ($searchVoteAwardSettings as $searchVoteAwardSetting){
                    $voteAwardName = VoteAward::find($searchVoteAwardSetting->voteAward_id)->name;
                    $count = $collection->where('award_id' , $searchAwardSetting->award_id )->where('vote_award_id' , $searchVoteAwardSetting->voteAward_id)
                 //   filter(function ($item)use($searchAwardSetting , $searchVoteAwardSetting) {
              //          return $item->award_id == $searchAwardSetting->award_id && $item->vote_award_id == $searchVoteAwardSetting->voteAward_id;
             //       })
                        ->unique('works_id')->count();
                    $result[$awardName][$voteAwardName] =  $count;
                }
                $num = $collection->filter(function ($item)use($searchAwardSetting) {
                    return $item->award_id == $searchAwardSetting->award_id ;
                })->unique('works_id')->count();
                $result[$awardName]['作品总计'] =  $num;
            }
            return json_encode($result, JSON_UNESCAPED_UNICODE );
        });
        return json_decode($staticResult, true);





        //方法四
        /**
        $staticResult =  \Cache::tags(self::CACHE_TAG)->rememberForever('ggr-school:' . $this->school_id . '-' . $this->dept_id . '-' .$this->speciality_id , function (){
            $result = [];
            $searchVoteAwardSettings = DB::table('xyj_works_award_search_setting')->groupBy('voteAward_id')->lists('voteAward_id');
            $countNumbers = DB::table('works_search_view')->select(DB::raw('award_id , vote_award_id , count(works_id) as count_number , dept_id , speciality_id ,school_id ,award_name , vote_award_name'))
                ->where(['school_id' => $this->school_id , 'dept_id' => $this->dept_id , 'speciality_id' => $this->speciality_id])
                ->whereIn('vote_award_id' , $searchVoteAwardSettings)
                ->groupBy('award_id' , 'vote_award_id')->get();
            foreach ($countNumbers as $countNumber){
                $result[$countNumber->award_name][$countNumber->vote_award_name] =  $countNumber->count_number;
            }
            $countAwardNumbers = DB::table('works_search_view')->select(DB::raw('award_id ,  count(works_id) as count_number , dept_id , speciality_id , school_id , award_name , vote_award_name'))
                ->where(['school_id' => $this->school_id , 'dept_id' => $this->dept_id , 'speciality_id' => $this->speciality_id])
                ->groupBy('award_id')->get();
            foreach ($countAwardNumbers as $countAwardNumber){
                $result[$countAwardNumber->award_name]['作品总计'] =  $countAwardNumber->count_number;
            }
            return json_encode($result, JSON_UNESCAPED_UNICODE );
        });
        return json_decode($staticResult, true);

         *
         * **/

    }

    public static function getGgrSchoolList()
    {
        $staticResult =  \Cache::tags(self::LIST_CACHE_TAG)->rememberForever('ggr-school-list:' , function (){
            $list = [];
            $models = self::all();
            foreach ($models as $model){
                $schoolName = isset($model->school) ? $model->school->name : '学校出错';
                $teacherName = isset($model->teacher) ? $model->teacher->name : '老师出错';
                $ggrName = isset($model->ggr) ? $model->ggr->name : '责编出错';
                $list[$model->id] = $schoolName . '-' . $teacherName . '-' . $ggrName;
            }
            return json_encode($list, JSON_UNESCAPED_UNICODE );
        });
        return json_decode($staticResult, true);
    }
}
